﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
using System.IO;
using CTEM.Controllers.Global;

using CTEM.Models;
namespace CTEM.Controllers.DAO
{
    class LedgerDAO
    {

        public SqlTransaction Transaction { get; set; }
        private SqlConnection _con;

        SqlConnection Connection
        {
            get
            {
                if (_con == null)
                    _con = new SqlConnection(DatabaseInfo.ConnectionString);
                if (_con.State == ConnectionState.Closed)
                {
                    _con.Open();
                }
                return _con;
            }
            set
            {
                _con = value;
            }
        }
        public List<LedgerDTO> LoadAllLedger()
        {
            var cmd = new SqlCommand("[dbo].[Ledger_GetAll]", Connection);

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                List<LedgerDTO> listcons = new List<LedgerDTO>();
                while (reader.Read())
                {
                    LedgerDTO dto = new LedgerDTO
                    {
                        LedgerID = Convert.ToInt64(reader["LedgerID"]),
                        Name = Convert.ToString(reader["Name"]),
                        Type = Convert.ToString(reader["Type"]),
                        Number = Convert.ToInt64(reader["Number"]),
                        Person = Convert.ToString(reader["Person"]),
                        Reason = Convert.ToString(reader["Reason"]),
                        Method = Convert.ToString(reader["Method"]),
                        Date = Convert.ToDateTime(reader["Date"]),
                        Kind = reader["Kind"] == DBNull.Value ? 0 :
                        Convert.ToInt64(reader["Kind"]),
                        FromAcc = Convert.ToString(reader["FromAcc"]),
                        ToAcc = Convert.ToString(reader["ToAcc"]),
                        FromNum = Convert.ToString(reader["FromNum"]),
                        ToNum = Convert.ToString(reader["ToNum"]),
                        FromBank = Convert.ToString(reader["FromBank"]),
                        ToBank = Convert.ToString(reader["ToBank"]),
                        SendDate = reader["SendDate"] == DBNull.Value ? DateTime.Today :
                        Convert.ToDateTime(reader["SendDate"]),
                        Sender = Convert.ToString(reader["Sender"]),
                        Taker = Convert.ToString(reader["Taker"]),
                    };
                    try
                    {
                        dto.SpendTypeName = Convert.ToString(reader["SpendTypeName"]);
                    }
                    catch { }

                    listcons.Add(dto);
                }
                return listcons;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, "Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }
        }
        public LedgerDTO GetLedgerByID(long ledgerID)
        {
            var cmd = new SqlCommand("[dbo].[Ledger_GetLedgerByID]", Connection);

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@LedgerID", ledgerID));
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                var ledgerDto = new LedgerDTO();
                while (reader.Read())
                {

                    ledgerDto.LedgerID = Convert.ToInt64(reader["LedgerID"]);
                    ledgerDto.Name = Convert.ToString(reader["Name"]);
                    ledgerDto.Type = Convert.ToString(reader["Type"]);
                    ledgerDto.Number = Convert.ToInt64(reader["Number"]);
                    ledgerDto.Person = Convert.ToString(reader["Person"]);
                    ledgerDto.Reason = Convert.ToString(reader["Reason"]);
                    ledgerDto.Method = Convert.ToString(reader["Method"]);
                    ledgerDto.Date = Convert.ToDateTime(reader["Date"]);
                    ledgerDto.Kind = reader["Kind"] == DBNull.Value
                                         ? 0
                                         : Convert.ToInt64(reader["Kind"]);
                    ledgerDto.FromAcc = Convert.ToString(reader["FromAcc"]);
                    ledgerDto.ToAcc = Convert.ToString(reader["ToAcc"]);
                    ledgerDto.FromNum = Convert.ToString(reader["FromNum"]);
                    ledgerDto.ToNum = Convert.ToString(reader["ToNum"]);
                    ledgerDto.FromBank = Convert.ToString(reader["FromBank"]);
                    ledgerDto.ToBank = Convert.ToString(reader["ToBank"]);
                    ledgerDto.SendDate = reader["SendDate"] == DBNull.Value
                                             ? DateTime.Today
                                             : Convert.ToDateTime(reader["SendDate"]);
                    ledgerDto.Sender = Convert.ToString(reader["Sender"]);
                    ledgerDto.Taker = Convert.ToString(reader["Taker"]);
                    
                 
                }
                return ledgerDto;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, "Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }
        }
        public List<LedgerDTO> LedgerSearch(LedgerDTO param)
        {
            var cmd = new SqlCommand("[dbo].[Ledger_search]", Connection);

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            if (param.Name.Trim().Equals("")==false)
                cmd.Parameters.Add(new SqlParameter("@Name", "%" + param.Name.Trim() + "%"));
            else
                cmd.Parameters.Add(new SqlParameter("@Name", DBNull.Value));
            if (param.Type.Trim().Equals("") == false && param.Type.Trim().Equals("Tất cả") == false)
                cmd.Parameters.Add(new SqlParameter("@Type",param.Type.Trim()));
            else
                cmd.Parameters.Add(new SqlParameter("@Type", DBNull.Value));
            if (param.Person.Trim().Equals("") == false)
                cmd.Parameters.Add(new SqlParameter("@Person", "%" + param.Person.Trim() + "%"));
            else
                cmd.Parameters.Add(new SqlParameter("@Person", DBNull.Value));
            if (param.Kind!=0)
                cmd.Parameters.Add(new SqlParameter("@Kind", param.Kind));
            else
                cmd.Parameters.Add(new SqlParameter("@Kind", DBNull.Value));
            cmd.Parameters.Add(new SqlParameter("@FromDate", param.fromDate));
            cmd.Parameters.Add(new SqlParameter("@ToDate", param.toDate));
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                List<LedgerDTO> listcons = new List<LedgerDTO>();
                while (reader.Read())
                {
                    LedgerDTO dto = new LedgerDTO
                    {
                        LedgerID = Convert.ToInt64(reader["LedgerID"]),
                        Name = Convert.ToString(reader["Name"]),
                        Type = Convert.ToString(reader["Type"]),
                        Number = Convert.ToInt64(reader["Number"]),
                        Person = Convert.ToString(reader["Person"]),
                        Reason = Convert.ToString(reader["Reason"]),
                        Method = Convert.ToString(reader["Method"]),
                        Date = Convert.ToDateTime(reader["Date"]),
                        Kind =reader["Kind"]==DBNull.Value?0:
                        Convert.ToInt64(reader["Kind"]),
                        SpendTypeName = Convert.ToString(reader["SpendTypeName"]),
                        FromAcc = Convert.ToString(reader["FromAcc"]),
                        ToAcc = Convert.ToString(reader["ToAcc"]),
                        FromNum = Convert.ToString(reader["FromNum"]),
                        ToNum = Convert.ToString(reader["ToNum"]),
                        FromBank = Convert.ToString(reader["FromBank"]),
                        ToBank = Convert.ToString(reader["ToBank"]),
                        SendDate =reader["SendDate"]==DBNull.Value?DateTime.Today:
                        Convert.ToDateTime(reader["SendDate"]),
                        Sender = Convert.ToString(reader["Sender"]),
                        Taker = Convert.ToString(reader["Taker"]),



                    };
                  
                    listcons.Add(dto);
                }
                return listcons;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, "Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }
        }
        public long CreateLedger(LedgerDTO dto)
        {
            var cmd = new SqlCommand("[dbo].[Ledger_Create]", Connection) { CommandType = CommandType.StoredProcedure };
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            try
            {
                cmd.Parameters.Add(new SqlParameter("@Name", dto.Name.Trim()));
                cmd.Parameters.Add(new SqlParameter("@Type", dto.Type.Trim()));
                cmd.Parameters.Add(new SqlParameter("@Number", dto.Number));
                cmd.Parameters.Add(new SqlParameter("@Person", dto.Person.Trim()));
                cmd.Parameters.Add(new SqlParameter("@Reason", dto.Reason));
                cmd.Parameters.Add(new SqlParameter("@Method", dto.Method));
                cmd.Parameters.Add(new SqlParameter("@Date", dto.Date));
                cmd.Parameters.Add(new SqlParameter("@Kind", dto.Kind));
                cmd.Parameters.Add(new SqlParameter("@FromAcc", dto.FromAcc));
                cmd.Parameters.Add(new SqlParameter("@ToAcc", dto.ToAcc));
                cmd.Parameters.Add(new SqlParameter("@FromNum", dto.FromNum));
                cmd.Parameters.Add(new SqlParameter("@ToNum", dto.ToNum));
                cmd.Parameters.Add(new SqlParameter("@FromBank", dto.FromBank));
                cmd.Parameters.Add(new SqlParameter("@ToBank", dto.ToBank));
                cmd.Parameters.Add(new SqlParameter("@Sender", dto.Sender));
                cmd.Parameters.Add(new SqlParameter("@Taker", dto.Taker));
                cmd.Parameters.Add(new SqlParameter("@SendDate", dto.SendDate));

                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                    return Convert.ToInt64(reader[0]);
                return 0;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message);
                return 0;
            }
            finally
            {
                if (Transaction == null)
                    Connection.Close();
            }
        }
        public bool updateLedger(LedgerDTO dto)
        {
            var cmd = new SqlCommand("[dbo].[Ledger_Update]", Connection) { CommandType = CommandType.StoredProcedure };
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            try
            {
                cmd.Parameters.Add(new SqlParameter("@LedgerID", dto.LedgerID));
                cmd.Parameters.Add(new SqlParameter("@Name", dto.Name));
                cmd.Parameters.Add(new SqlParameter("@Type", dto.Type));
                cmd.Parameters.Add(new SqlParameter("@Number", dto.Number));
                cmd.Parameters.Add(new SqlParameter("@Person", dto.Person));
                cmd.Parameters.Add(new SqlParameter("@Reason", dto.Reason));
                cmd.Parameters.Add(new SqlParameter("@Method", dto.Method));
                cmd.Parameters.Add(new SqlParameter("@Date", dto.Date));
                cmd.Parameters.Add(new SqlParameter("@Kind", dto.Kind));
                cmd.Parameters.Add(new SqlParameter("@FromAcc", dto.FromAcc));
                cmd.Parameters.Add(new SqlParameter("@ToAcc", dto.ToAcc));
                cmd.Parameters.Add(new SqlParameter("@FromNum", dto.FromNum));
                cmd.Parameters.Add(new SqlParameter("@ToNum", dto.ToNum));
                cmd.Parameters.Add(new SqlParameter("@FromBank", dto.FromBank));
                cmd.Parameters.Add(new SqlParameter("@ToBank", dto.ToBank));
                cmd.Parameters.Add(new SqlParameter("@Sender", dto.Sender));
                cmd.Parameters.Add(new SqlParameter("@Taker", dto.Taker));
                cmd.Parameters.Add(new SqlParameter("@SendDate", dto.SendDate));


                cmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message);
                return false;
            }
            finally
            {
                if (Transaction == null)
                    Connection.Close();
            }
        }
        public bool delete(long id)
        {
            var cmd = new SqlCommand("[dbo].[Ledger_Delete]", Connection) { CommandType = CommandType.StoredProcedure };
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            try
            {
                cmd.Parameters.Add(new SqlParameter("@LedgerID", id));
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message);
                return false;
            }
            finally
            {
                if (Transaction == null)
                    Connection.Close();
            }
        }


        public long CreateSpendType(SendTypeDTO dto)
        {
            var cmd = new SqlCommand("[dbo].[SpendType_Create]", Connection) { CommandType = CommandType.StoredProcedure };
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            try
            {
                cmd.Parameters.Add(new SqlParameter("@SpendTypeName", dto.SpendTypeName));
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                    return Convert.ToInt64(reader[0]);
                return 0;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message);
                return 0;
            }
            finally
            {
                if (Transaction == null)
                    Connection.Close();
            }
        }
        public bool UpdateSpendType(SendTypeDTO dto)
        {
            var cmd = new SqlCommand("[dbo].[SpendType_Update]", Connection) { CommandType = CommandType.StoredProcedure };
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            try
            {
                cmd.Parameters.Add(new SqlParameter("@SpendTypeID", dto.SpendTypeID));
                cmd.Parameters.Add(new SqlParameter("@SpendTypeName", dto.SpendTypeName));
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message);
                return false;
            }
            finally
            {
                if (Transaction == null)
                    Connection.Close();
            }
        }
        public bool DeleteSpendType(SendTypeDTO dto)
        {
            var cmd = new SqlCommand("[dbo].[SpendType_Delete]", Connection) { CommandType = CommandType.StoredProcedure };
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            try
            {
                cmd.Parameters.Add(new SqlParameter("@SpendTypeID", dto.SpendTypeID));
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message);
                return false;
            }
            finally
            {
                if (Transaction == null)
                    Connection.Close();
            }
        }
        public List<SendTypeDTO> LoadSendType()
        {
            var cmd = new SqlCommand("[dbo].[SpendType_Getall]", Connection);

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                List<SendTypeDTO> listcons = new List<SendTypeDTO>();
                while (reader.Read())
                {
                    SendTypeDTO dto = new SendTypeDTO
                    {
                        SpendTypeID = Convert.ToInt64(reader["SpendTypeID"]),
                        SpendTypeName = Convert.ToString(reader["SpendTypeName"]),
                        


                    };
                    
                    listcons.Add(dto);
                }
                return listcons;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, "Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }
        }

        public bool loadLergerByKindID(long kindID)
        {
            var cmd = new SqlCommand("[dbo].[Ledger_GetLedgerKindID]", Connection);

            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@kind", kindID));
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
              
                return reader.Read();
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, "Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return false;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }
        }

        public List<LedgerDTO> loadLergerforReport(long kindID, string type, DateTime formday, DateTime toDay)
        {
            var cmd = new SqlCommand("[dbo].[Ledger_GetforReport]", Connection);
            cmd.Parameters.Add(new SqlParameter("@Kind", kindID));
            cmd.Parameters.Add(new SqlParameter("@Type", type));
            cmd.Parameters.Add(new SqlParameter("@Formday", formday));
            cmd.Parameters.Add(new SqlParameter("@ToDay", toDay));
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                List<LedgerDTO> listcons = new List<LedgerDTO>();
                while (reader.Read())
                {
                    LedgerDTO dto = new LedgerDTO
                    {
                        LedgerID = Convert.ToInt64(reader["LedgerID"]),
                        Name = Convert.ToString(reader["Name"]),
                        Type = Convert.ToString(reader["Type"]),
                        Number = Convert.ToInt64(reader["Number"]),
                        Person = Convert.ToString(reader["Person"]),
                        Reason = Convert.ToString(reader["Reason"]),
                        Method = Convert.ToString(reader["Method"]),
                        Date = Convert.ToDateTime(reader["Date"]),
                        Kind = reader["Kind"] == DBNull.Value ? 0 :
                        Convert.ToInt64(reader["Kind"]),
                        FromAcc = Convert.ToString(reader["FromAcc"]),
                        ToAcc = Convert.ToString(reader["ToAcc"]),
                        FromNum = Convert.ToString(reader["FromNum"]),
                        ToNum = Convert.ToString(reader["ToNum"]),
                        FromBank = Convert.ToString(reader["FromBank"]),
                        ToBank = Convert.ToString(reader["ToBank"]),
                        SendDate = reader["SendDate"] == DBNull.Value ? DateTime.Today :
                        Convert.ToDateTime(reader["SendDate"]),
                        Sender = Convert.ToString(reader["Sender"]),
                        Taker = Convert.ToString(reader["Taker"]),
                    };
                    try
                    {
                        dto.SpendTypeName = Convert.ToString(reader["SpendTypeName"]);
                    }
                    catch { }
             
                    listcons.Add(dto);
                }
                return listcons;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, "Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }
        
        }

        public List<LedgerDTO> loadLergerforAllReport( string type, DateTime formday, DateTime toDay)
        {
            var cmd = new SqlCommand("[dbo].[Ledger_GetforAllReport]", Connection);
           
            cmd.Parameters.Add(new SqlParameter("@Type", type));
            cmd.Parameters.Add(new SqlParameter("@Formday", formday));
            cmd.Parameters.Add(new SqlParameter("@ToDay", toDay));
            if (Transaction != null)
            {
                cmd.Transaction = Transaction;
            }
            cmd.CommandType = CommandType.StoredProcedure;
            try
            {
                SqlDataReader reader = cmd.ExecuteReader();
                List<LedgerDTO> listcons = new List<LedgerDTO>();
                while (reader.Read())
                {
                    LedgerDTO dto = new LedgerDTO
                    {
                        LedgerID = Convert.ToInt64(reader["LedgerID"]),
                        Name = Convert.ToString(reader["Name"]),
                        Type = Convert.ToString(reader["Type"]),
                        Number = Convert.ToInt64(reader["Number"]),
                        Person = Convert.ToString(reader["Person"]),
                        Reason = Convert.ToString(reader["Reason"]),
                        Method = Convert.ToString(reader["Method"]),
                        Date = Convert.ToDateTime(reader["Date"]),
                        Kind = reader["Kind"] == DBNull.Value ? 0 :
                        Convert.ToInt64(reader["Kind"]),
                        FromAcc = Convert.ToString(reader["FromAcc"]),
                        ToAcc = Convert.ToString(reader["ToAcc"]),
                        FromNum = Convert.ToString(reader["FromNum"]),
                        ToNum = Convert.ToString(reader["ToNum"]),
                        FromBank = Convert.ToString(reader["FromBank"]),
                        ToBank = Convert.ToString(reader["ToBank"]),
                        SendDate = reader["SendDate"] == DBNull.Value ? DateTime.Today :
                        Convert.ToDateTime(reader["SendDate"]),
                        Sender = Convert.ToString(reader["Sender"]),
                        Taker = Convert.ToString(reader["Taker"]),
                    };
                    try
                    {
                        dto.SpendTypeName = Convert.ToString(reader["SpendTypeName"]);
                    }
                    catch { }
              
                    listcons.Add(dto);
                }
                return listcons;
            }
            catch (SqlException sql)
            {
                MessageBox.Show(sql.Message, "Error SQL", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                if (Transaction == null) Connection.Close();
            }

        }
    }
}
